//
//  DataBaseHandle.m
//  舒适家
//
//  Created by dllo on 15/10/6.
//  Copyright (c) 2015年 lsc. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle
+(instancetype)shareDataBaseHandle
{
        static DataBaseHandle *data=nil;
        static dispatch_once_t onceToken;
        dispatch_once(&onceToken, ^{
            data=[[DataBaseHandle alloc]init];
        });
        return data;
}
static sqlite3 *db=nil;
//创建数据库
-(void)openDB1
{
    if (db!=nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath=[array lastObject];
    
    NSString *sqlFilePath = [filePath stringByAppendingPathComponent:@"liwu.sqlite"];
    NSLog(@"====%@",sqlFilePath);
    int result=sqlite3_open(sqlFilePath.UTF8String, &db);
    if (result==SQLITE_OK) {
        NSLog(@"打开数据库成功");
    }else
    {
        NSLog(@"打开数据库失败");
    }
    
    
}

//创建表
-(void)createAllTable
{
    NSString *sql=@"CREATE TABLE IF NOT EXISTS liwu(number INTEGER PRIMARY KEY AUTOINCREMENT,content_url TEXT,cover_image_url TEXT,short_title TEXT,title TEXT,share_msg TEXT,likes_count INTEGER)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result==SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
    
}

//插入表
-(void)insertCellModelTable:(Model *)model
{

    
    NSString *sql=[NSString stringWithFormat:@"INSERT INTO liwu(content_url ,cover_image_url ,short_title ,title,share_msg,likes_count) VALUES('%@','%@','%@','%@','%@','%ld')",model.content_url,model.cover_image_url,model.short_title,model.title,model.share_msg,model.likes_count];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result==SQLITE_OK) {
        NSLog(@"收藏成功");
    }
    else
    {
        NSLog(@"收藏失败");
    }
    
}

//查询表
-(NSMutableArray *)select
{
    //存放查询到得model信息
    NSMutableArray *array=[NSMutableArray array];
    NSString *sql=@"SELECT * FROM liwu";
    //创建数据库查询跟随指针
    sqlite3_stmt *stmt=nil;
    //查询前准备，主要是将数据库对象，sql语句，跟随指针stmt关联到一起
    //参数1：数据库对象  参数2：sql语句  参数3：设置sql语句长度限制，-1为不限制  参数4：跟随指针  参数5：备用参数
    int  result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result==SQLITE_OK)
    {

        //遍历表中每一行
        while (sqlite3_step(stmt)==SQLITE_ROW)
        {
            Model *model=[[Model alloc]init];

            //取出某一行中第一列的数据(编号)
            int number=sqlite3_column_int(stmt, 0);
            //取出某一行的第二,三,四,五,六列
            const unsigned char   *content_url = sqlite3_column_text(stmt, 1);
            const unsigned char   *cover_image_url = sqlite3_column_text(stmt, 2);
            const unsigned char   *short_title = sqlite3_column_text(stmt, 3);
            const unsigned char   *title = sqlite3_column_text(stmt, 4);
            const unsigned char   *share_msg = sqlite3_column_text(stmt, 5);
                          int likes_count = sqlite3_column_int(stmt, 6);
            //装成OC型
            NSInteger numInteger = number;
            NSString *content_urlStr=[NSString stringWithUTF8String:(const char *)content_url];
            NSString *cover_image_urlStr=[NSString stringWithUTF8String:(const char *)cover_image_url];
            NSString *short_titleStr=[NSString stringWithUTF8String:(const char *)short_title];
            NSString *titleStr=[NSString stringWithUTF8String:(const char *)title];
            NSString *share_msgStr=[NSString stringWithUTF8String:(const char *)share_msg];
            NSInteger likes_countInteger = likes_count;
            model.content_url=content_urlStr;
            model.cover_image_url=cover_image_urlStr;
            model.short_title=short_titleStr;
            model.title=titleStr;
            model.share_msg=share_msgStr;
            model.likes_count=likes_countInteger;
            [array addObject:model];
        }

        NSLog(@"查询表成功");
    }else
    {
        NSLog(@"查询表失败");
    }
    return array;
}

//删除
-(void)deleteTable:(NSString *)content_url
{
    NSString *sql=[NSString stringWithFormat:@"DELETE FROM liwu WHERE content_url='%@'",content_url];
    int result=sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result==SQLITE_OK) {
        NSLog(@"删除成功");
    }else
    {
        NSLog(@"删除失败");
    }
}


//删除表
-(void)dorpTable
{
    NSString *sql=@"DROP TABLE liwu";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else
    {
        NSLog(@"删除表失败");
    }
}


//关闭数据库
-(void)closeDB1
{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    }else
    {
        NSLog(@"数据库关闭失败");
    }
}


@end
